using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public class DAL
{
    private static OleDbConnection GetConnection()
    {
        OleDbConnection Connection = new OleDbConnection();
        Connection.ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
        Connection.Open();
        return Connection;
    }

    public static DataTable GetItems(int ItemsPerPage, int PageNumber, out int MaxItems)
    {
        // returns a specified page of items
        DataSet Set = new DataSet();
        OleDbDataAdapter Adapter;
        DataTable ItemResults;
        OleDbConnection Connection = GetConnection();

        ItemResults = new DataTable("Results");
        ItemResults.Columns.Add(new DataColumn("ID", System.Type.GetType("System.Int32")));
        ItemResults.Columns.Add(new DataColumn("Name", System.Type.GetType("System.String")));
        ItemResults.Columns.Add(new DataColumn("Thumbnail", System.Type.GetType("System.String")));

        Adapter = new OleDbDataAdapter("SELECT [Items].[ID], [Items].[Name], [Items].[Thumbnail] FROM [Items] ORDER BY [Name]", Connection);
        Adapter.Fill(Set);

        for (int i = 0; i < ItemsPerPage; i++)
        {
            int Index = i + (ItemsPerPage * (PageNumber - 1));

            if (Index < Set.Tables[0].Rows.Count)
            {
                ItemResults.ImportRow(Set.Tables[0].Rows[Index]);
            }
        }
        MaxItems = Set.Tables[0].Rows.Count;
        
        Adapter.Dispose();
        Connection.Dispose();
        return ItemResults;
    }

    public static DataTable GetItems(int ItemsPerPage, int PageNumber, int CategoryID, out int MaxItems)
    {
        // returns a specified page of items from a specified category
        DataSet Set = new DataSet();
        OleDbDataAdapter Adapter;
        DataTable ItemResults;
        OleDbConnection Connection = GetConnection();

        ItemResults = new DataTable("Results");
        ItemResults.Columns.Add(new DataColumn("ID", System.Type.GetType("System.Int32")));
        ItemResults.Columns.Add(new DataColumn("Name", System.Type.GetType("System.String")));
        ItemResults.Columns.Add(new DataColumn("Thumbnail", System.Type.GetType("System.String")));

        Adapter = new OleDbDataAdapter("SELECT [Items].[ID], [Items].[Name], [Items].[Thumbnail] FROM [Items], [Categories], [Item_Category] WHERE [Items].[ID] = [Item_Category].[Item_ID] AND [Categories].[ID] = [Item_Category].[Category_ID] AND [Categories].[ID] = " + CategoryID.ToString(), Connection);
        Adapter.Fill(Set);

        for (int i = 0; i < ItemsPerPage; i++)
        {
            int Index = i + (ItemsPerPage * (PageNumber - 1));

            if (Index < Set.Tables[0].Rows.Count)
            {
                ItemResults.ImportRow(Set.Tables[0].Rows[Index]);
            }
        }
        MaxItems = Set.Tables[0].Rows.Count;

        Adapter.Dispose();
        Connection.Dispose();
        return ItemResults;
    }

    public static OleDbDataReader GetCategories()
    {
        // returns a list of categories
        OleDbConnection Connection = GetConnection();
        OleDbCommand Command = new OleDbCommand();
        
        Command.Connection = Connection;
        Command.CommandType = System.Data.CommandType.Text;
        Command.CommandText = "SELECT [ID], [Name] FROM [Categories] ORDER BY [Name]";

        OleDbDataReader CategoryResults = Command.ExecuteReader(CommandBehavior.CloseConnection);

        Command.Dispose();
        return CategoryResults;
    }
    public static OleDbDataReader GetItemDetails(int ItemID)
    {
        // returns detailed information about one item
        OleDbConnection Connection = GetConnection();
        OleDbCommand Command = new OleDbCommand();
        
        Command.Connection = Connection;
        Command.CommandType = System.Data.CommandType.Text;
        Command.CommandText = "SELECT [Items].[ID], [Items].[Name], [Items].[Thumbnail], [Items].[Description], [Items].[Size] FROM [Items] WHERE [Items].[ID] = " + ItemID.ToString();

        OleDbDataReader ItemDetailResults = Command.ExecuteReader(CommandBehavior.CloseConnection);

        Command.Dispose();
        return ItemDetailResults;
    }
}